package com.gzedu.common.db;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.util.StringUtils;

import com.gzedu.common.util.PropertyUtil;
import com.gzedu.common.util.page.Page;

public class DBResult {
	
	public static List<Map<String,Object>> getResult(String sql){
		
		List<Map<String,Object>> res = new ArrayList<Map<String,Object>>();
		
		DBHelper dbHelper = new DBHelper(sql);
		
		try{
			ResultSet rs = dbHelper.pst.executeQuery();
			ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData(); 
			int columnCount = md.getColumnCount(); 
			while (rs.next()) {       
				Map<String, Object> rowData = new HashMap<String, Object>();       
				for (int i = 1; i <= columnCount; i++) {         
					rowData.put(md.getColumnName(i), rs.getObject(i));       
				}       
				res.add(rowData);     
			} 
		}catch(Exception e){
			e.printStackTrace();
		}
		return res;
	}
	
	/**
	 * 查询数据总数
	 * @param sql
	 * @return
	 */
	public static int findCount(String sql){
		int count = 0;
		String countSql = "select count(1) from (" + sql + ") tab";
		try{
			DBHelper dbHelper = new DBHelper(countSql);
			ResultSet rs = dbHelper.pst.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 查询数据（分页）
	 * @param sql
	 * @param page
	 * @return
	 */
	public static List<Map<String,Object>> getPageRes(String sql, String orderBy, Page page){
		String sqlFlag = new PropertyUtil().readProperty("driver").toLowerCase();
		List<Map<String,Object>> res = new ArrayList<Map<String,Object>>();
		int[] params = new int[2];
		//if("mysql".indexOf(sqlFlag) != -1){ //mysql数据库
			if(StringUtils.isEmpty(orderBy)){
				sql = sql + " order by create_date DESC ";
			}else{
				sql = sql + " order by "+orderBy+ " DESC ";
			}
			sql = sql + " limit ?,?";
			System.out.println(sql);
			int pageIndex = DBResult.pageIndex(page.getPageNo(), page.getPageSize());
			params[0] = pageIndex;
			params[1] = page.getPageSize();
			DBHelper dbHelper = new DBHelper(sql,params);
			try{
				ResultSet rs = dbHelper.pst.executeQuery();
				ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData(); 
				int columnCount = md.getColumnCount(); 
				while (rs.next()) {       
					Map<String, Object> rowData = new HashMap<String, Object>();       
					for (int i = 1; i <= columnCount; i++) {
						if("create_date".equals(md.getColumnName(i))){
							rowData.put(md.getColumnName(i), changeDateToStr(rs.getObject(i)));
						}else{
							rowData.put(md.getColumnName(i), rs.getObject(i));   
						}
					}       
					res.add(rowData);     
				} 
			}catch(Exception e){
				e.printStackTrace();
			}
//		}else if("oracle".indexOf(sqlFlag) != -1){
//			try{
//				
//			}catch(Exception e){
//				e.printStackTrace();
//			}
//		}
		return res;
	}
	
	
	private static int pageIndex(int pageNo, int pageSize){
		return pageNo * pageSize - pageSize;
	}
	
	/**
	 * 根据sql获取字段名称
	 * @param sql
	 * @return
	 */
	public static List<String> getColInfo(String sql){
		List<String> res = new ArrayList<String>();
		sql += " limit 1,1";
		DBHelper dbHelper = new DBHelper(sql);
		
		try{
			ResultSetMetaData md = (ResultSetMetaData) dbHelper.pst.executeQuery().getMetaData(); 
			int columnCount = md.getColumnCount(); 
			
			for (int i = 1; i <= columnCount; i++) {         
				 res.add(md.getColumnName(i));       
			}       
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return res;
	}
	
	
	private static String changeDateToStr(Object obj){
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
		if(obj instanceof Date){
			return sdf.format((Date)obj);
		}
		return null; 
	}
	
}
